package com.andy.utils;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.NotOLE2FileException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.andy.vo.ExamUserVO;

/**
 * @author AndyZheng
 * Excel 操作工具类
 */
public class ExcelUtils {
	
	public static final String OFFICE_EXCEL_2003_POSTFIX = ".xls";
	public static final String OFFICE_EXCEL_2007_POSTFIX = ".xlsx";

	/**
	 * 根据文件路径创建Excel Workbook
	 * @param path
	 * @return
	 */
	public static Workbook openWorkbook(String path) {
		String postfix = path.substring(path.lastIndexOf("."));
		System.out.println(postfix);
		if (!"".equals(postfix)) {
			InputStream is = null;
			try {
				is = new FileInputStream(path);
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			}
			return openWorkbook(is, postfix);
		}
		return null;
	}
	
	/**
	 * 根据InputStream和Excel类型创建Excel Workbook
	 * @param is
	 * @param postfix
	 * @return
	 */
	public static Workbook openWorkbook(InputStream is, String postfix) {
		Workbook hssfWorkbook = null;
		try {
			if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
				hssfWorkbook = new HSSFWorkbook(is);	// 建立新Workbook对象
			} else if (OFFICE_EXCEL_2007_POSTFIX.equals(postfix)) {
				hssfWorkbook = new XSSFWorkbook(is);	// 建立新Workbook对象
			}
		} catch (Exception e) {
			if(e instanceof NotOLE2FileException){
				return null;
			}
			e.printStackTrace();
		}
		return hssfWorkbook;
	}
	
	/**
	 * 获取指定Excel表格的内容
	 * @param wk
	 * @return
	 */
	public static List<ExamUserVO> getStudentListByExcel(Workbook wk) {
		final String GENDER_MALE_STRING = "男", GENDER_FEMALE_STRING = "女";
		final Integer GENDER_MALE_INTEGER = 0, GENDER_FEMALE_INTEGER = 1;
		List<ExamUserVO> resultList = new ArrayList<ExamUserVO>();
		Sheet sheet = wk.getSheetAt(0);
		int maxCellNubmer = 0;
		int maxRowNumber = sheet.getLastRowNum();
		ExamUserVO vo = null;
		for (int currentRowNumber = 1; currentRowNumber <= maxRowNumber; currentRowNumber ++) {
			Row row = sheet.getRow(currentRowNumber);
			maxCellNubmer = row.getLastCellNum();
			vo = new ExamUserVO();
			for (int currentCellNumber = 0; currentCellNumber <  maxCellNubmer; currentCellNumber ++) {
				Cell cell = row.getCell(currentCellNumber);
				switch (currentCellNumber) {
					case 0: 
						// 获取学号列数据
						vo.setUserId((String) getCellValue(cell));
						break;
					case 1: 
						// 获取姓名列数据
						vo.setUserName((String) getCellValue(cell));
						break;
					case 2: 
						// 获取性别列数据
						
						String gender = (String) getCellValue(cell);
						// 判断值为“男”则存入0，值为“女”则存入1
						if (GENDER_MALE_STRING.equals(gender)) {
							vo.setGender(GENDER_MALE_INTEGER );	// 男
						} else if (GENDER_FEMALE_STRING.equals(gender)) {
							vo.setGender(GENDER_FEMALE_INTEGER ); // 女
						}
						break;
					case 3: 
						// 获取联系电话列数据
						vo.setTel((String) getCellValue(cell));
						break;
					case 4: 
						// 获取电子邮箱列数据
						vo.setEmail((String) getCellValue(cell));
						break;
					case 5: 
						// 获取地址列数据
						vo.setAddress((String) getCellValue(cell));
						break;
					case 6: 
						// 获取出生日期列数据
						vo.setBirthday((Date) getCellValue(cell));
						break;
					case 7: 
						// 获取密码列数据
						String decodingPassword = ((String) getCellValue(cell));
						vo.setPassword(MD5Utils.GetMD5Code(decodingPassword));
						break;
				}
			}
			resultList.add(vo);
		}
		return resultList;
	}
	
	/**
	 * 获取单元格的值
	 * @param cell
	 * @return
	 */
	@SuppressWarnings({ "deprecation" })
	private static Object getCellValue(Cell cell) {
		switch (cell.getCellType()) {
			// 空白
			case Cell.CELL_TYPE_BLANK:
				return "";
			
			// 字符类型
			case Cell.CELL_TYPE_STRING:
				return cell.getStringCellValue();
				
			// 布尔类型
			case Cell.CELL_TYPE_BOOLEAN:
				return cell.getBooleanCellValue();
				
			// 错误
			case Cell.CELL_TYPE_ERROR:
				return null;
				
			// 数字类型
			case Cell.CELL_TYPE_NUMERIC:
				if (DateUtil.isCellDateFormatted(cell)) {
					Date theDate = cell.getDateCellValue();
					return theDate;
				} else {
					return cell.getNumericCellValue();
				}
			default:
				return null;
		}
	}

}
